﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: November 13, 2015
-- Description:	Writes new data to the specified entity.  A -1 for the data id specifies the entity will be created.
--				If the entity is to be created, the JSONData must include a relationship definition (Entity1ID and RelationshipName)
--				This relationship must include a "write chain" from the RequestorID to the new entity or the process will be rolled back.
-- =============================================
CREATE PROCEDURE [dbo].[WriteDataToDB_EntityAttributes] 
	-- Add the parameters for the stored procedure here
	@RequestorEntityID int, 
	@DataID int,
	@JSONData nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @EntityType nvarchar(100)
	DECLARE @EntitySubType nvarchar(100)
	DECLARE @Entity1ID int, @Entity2ID int, @ShortRelationshipName nvarchar(12)
	DECLARE @StartDate datetime, @EndDate datetime, @RequestedAction nvarchar(20)
	DECLARE @ErrorString nvarchar(255), @DeleteOverride nvarchar(10)
	DECLARE @DeleteAttribute nvarchar(255)

	SELECT @EntityType = StringValue FROM #JSONTable WHERE [NAME] = 'EntityType' AND StringValue <> 'null'
	-- If the entity is to be created, 
	IF @EntityType IS NULL
		IF @DataID <> -1
			SELECT @EntityType = EntityType, @EntitySubType = EntitySubType FROM Entities WHERE EntityID = @DataID
		ELSE
			SET @ErrorString = '{"DataError":"' + 'No Entity Type specified.' + '"}'
	-- It doesn't matter if the subtype is null.
	SELECT @EntitySubType = StringValue FROM #JSONTable WHERE [NAME] = 'EntitySubType' AND StringValue <> 'null'
	SELECT @DeleteAttribute = StringValue FROM #JSONTable WHERE [NAME] = 'DeleteAttribute' AND StringValue <> 'null'
	SELECT @DeleteOverride = StringValue FROM #JSONTable WHERE [NAME] = 'DeleteOverride' AND StringValue <> 'null'
	SELECT @RequestedAction = StringValue FROM #JSONTable WHERE [NAME] = 'RequestedAction' AND StringValue <> 'null'
	IF @RequestedAction IS NULL
		SET @RequestedAction = 'NotDelete'
	ELSE IF @RequestedAction <> 'DeleteEntity' AND @RequestedAction <> 'DeleteAttribute'
		SET @ErrorString = '{"DataError":"From EntityAttributes - Supported requested actions are DeleteEntity and DeleteAttribute"}'

	IF NOT @ErrorString IS NULL BEGIN
		DELETE FROM #JSONTable
		INSERT INTO #JSONTable
		SELECT * FROM parseJSON(@ErrorString)
		RETURN 1
	END

	BEGIN TRANSACTION WriteEntityData
	-- Create new entity
	IF @DataID = -1 AND @ErrorString IS NULL AND @RequestedAction = 'NotDelete'
	BEGIN
		BEGIN TRY
		INSERT INTO Entities(EntityType,EntitySubType)
		VALUES (@EntityType,@EntitySubtype)
		SELECT @DataID = SCOPE_IDENTITY()
		-- Put the new ID into the JSON table
		UPDATE #JSONTable SET StringValue = @DataID WHERE [NAME] = 'EntityID'
		-- Log the entity creation.
		INSERT INTO UndoOtherChanges(UndoAction,ChangedBy,WhenChanged,ChangeLog)
		VALUES('Create',@RequestorEntityID,GETDATE(),
			'Created new entity with an ID of ' + cast(@DataID AS nvarchar(10)) + ' entity type of ' + 
			@EntityType + ' and a subtype of ' +
			COALESCE(@EntitySubType,'') + ' that will eventually contain the following json data ' + 
			@JSONData);
		END TRY
		BEGIN CATCH
			SET @ErrorString = '{"DataError":"From EntityAttributes ' + REPLACE(ERROR_MESSAGE(),'"','''') + '"}'
		END CATCH
		IF @ErrorString IS NULL
		BEGIN
			EXEC dbo.WriteDataToDB_EntityRelationships @RequestorEntityID,'Create';
			SELECT @ErrorString = StringValue FROM #JSONTable WHERE [NAME] = 'DataError'
			IF NOT @ErrorString IS NULL
				SET @ErrorString = '{"DataError":"From EntityAttributes ' + @ErrorString + '"}'
		END
	END

	PRINT 'Security not fully implemented!'

	-- EntitySecurity is the permission that the RequestorEntityID has on the DataID
	DECLARE @EntitySecurity int, @EntityDeniedAttributes nvarchar(2000),@ApprovalValue bit
	--SELECT @EntitySecurity = MAX(PermissionLevel),@EntityDeniedAttributes = MAX(DeniedAttributes)
	--FROM dbo.ViewEntitySecurity(@RequestorEntityID,@DataID,2,DEFAULT)
	--IF @EntitySecurity > 3
	--	SET @ApprovalValue = 1
	--ELSE
	--	SET @ApprovalValue = NULL
	IF @RequestorEntityID = 0 BEGIN
		SET @ApprovalValue = 1
		SET @EntitySecurity = 5
		SET @EntityDeniedAttributes = NULL
	END


	-- Now that entity exists, begin inserting or updating attributes.
	IF @ErrorString IS NULL BEGIN
		-- because attributes can come from the subtype or the higher type, a temp
		-- table must be used to store the list of possible attributes.
		DECLARE @AttributesTable TABLE (
			AttributeID int,
			AttributeName nvarchar(255),
			DataType nvarchar(50)
		);
		DECLARE @AttributeID int,@AttributeName nvarchar(255),@DataType nvarchar(50)
		DECLARE @AttributeValue nvarchar(max)
		DECLARE @OldAttributeValue nvarchar(max)
		DECLARE @AttributeDataType nvarchar(50)
		DECLARE @AttributeQuery nvarchar(max)

		INSERT INTO @AttributesTable(AttributeID,AttributeName,DataType)
		SELECT AttributeID,AttributeName,DataType
		FROM DefinedAttributes WHERE EntityType = @EntityType AND EntitySubType IS NULL

		IF NOT @EntitySubType IS NULL
			INSERT INTO @AttributesTable(AttributeID,AttributeName,DataType)
			SELECT AttributeID,AttributeName,DataType
			FROM DefinedAttributes WHERE EntityType = @EntityType AND EntitySubType = @EntitySubType

		DECLARE @AttributesLoop CURSOR
		SET @AttributesLoop = CURSOR FAST_FORWARD
		FOR SELECT * FROM @AttributesTable
		
		-- Loop through each attribute, see if it exists in the #JSONTable. If it does, try to update it.
		OPEN @AttributesLoop
		FETCH NEXT FROM @AttributesLoop
		INTO @AttributeID,@AttributeName,@DataType
		WHILE @@FETCH_STATUS = 0 AND @ErrorString IS NULL
		BEGIN
			SET @AttributeValue = null
			SET @OldAttributeValue = null
			SELECT @AttributeValue = StringValue FROM #JSONTable WHERE [NAME] = @AttributeName AND StringValue <> 'null'

			-- @EntitySecurity less than 2 means no write access, and thus no ability to edit/create
			-- @EntityDeniedAttributes - if the attribute is in this list then this user is not allowed to edit/create
			IF @EntitySecurity < 2 AND CHARINDEX(@AttributeName,@EntityDeniedAttributes) <> 0 BEGIN
				SET @ErrorString = '{"DataError":"You do not have the ability to edit or create ' + @AttributeName + '."}'
			END

			IF @DataType = 'String'
				SELECT @OldAttributeValue = AttributeValueString FROM Attributes WHERE AttributeID = @AttributeID AND EntityID = @DataID
			ELSE IF @DataType = 'Boolean'
				SELECT @OldAttributeValue = IIF(AttributeValueBit = 0,'False','True') FROM Attributes WHERE AttributeID = @AttributeID AND EntityID = @DataID
			ELSE IF @DataType = 'Date'
				SELECT @OldAttributeValue = CAST(AttributeValueDateTime AS nvarchar(100)) FROM Attributes WHERE AttributeID = @AttributeID AND EntityID = @DataID
			ELSE IF @DataType = 'Number'
				SELECT @OldAttributeValue = CAST(AttributeValueNumber AS nvarchar(100)) FROM Attributes WHERE AttributeID = @AttributeID AND EntityID = @DataID
			ELSE IF @DataType = 'TimeDiv'
				SELECT @OldAttributeValue = AttributeValueString FROM Attributes WHERE AttributeID = @AttributeID AND EntityID = @DataID
			ELSE -- Probably Binary
				SET @ErrorString = '{"DataError":"' + @DataType + ' is currently not implemented."}'

			IF NOT @AttributeValue IS NULL AND @RequestedAction = 'NotDelete' AND @ErrorString IS NULL BEGIN
				IF @OldAttributeValue IS NULL BEGIN
					-- Insert Attribute
					BEGIN TRY
						IF @DataType = 'String'
							INSERT INTO Attributes(EntityID,AttributeID,AttributeValueString,Approved) 
							VALUES(@DataID,@AttributeID,@AttributeValue,@ApprovalValue)
						ELSE IF @DataType = 'Boolean'
							INSERT INTO Attributes(EntityID,AttributeID,AttributeValueBit,Approved) 
							VALUES(@DataID,@AttributeID,IIF(@AttributeValue = 'False',0,1),@ApprovalValue)
						ELSE IF @DataType = 'Date' BEGIN
							-- Make sure the date does not have a .0000 at the end, because that will break
							SET @AttributeValue = CASE WHEN CHARINDEX('.00',@AttributeValue) <> 0 
								THEN SUBSTRING(@AttributeValue,1,CHARINDEX('.00',@AttributeValue)-1) 
								ELSE @AttributeValue END
							INSERT INTO Attributes(EntityID,AttributeID,AttributeValueDateTime,Approved) 
							VALUES(@DataID,@AttributeID,CAST(@AttributeValue AS DateTime),@ApprovalValue)
						END ELSE IF @DataType = 'Number'
							INSERT INTO Attributes(EntityID,AttributeID,AttributeValueNumber,Approved) 
							VALUES(@DataID,@AttributeID,CAST(@AttributeValue AS numeric(18, 0)),@ApprovalValue)
						ELSE IF @DataType = 'TimeDiv'
							INSERT INTO Attributes(EntityID,AttributeID,AttributeValueString,Approved) 
							VALUES(@DataID,@AttributeID,@AttributeValue,@ApprovalValue)

						INSERT INTO UndoAttributes(EntityID,AttributeID,OldData,NewData,
						UndoAction,ChangedBy,WhenChanged)
						VALUES(@DataID,@AttributeID,null,@AttributeValue,
						'Create',@RequestorEntityID,GETDATE())
					END TRY
					BEGIN CATCH
						-- The replace removes " from the message and replaces it with ' to prevent issues parsing the JSON
						SET @ErrorString = '{"DataError":"From EntityAttributes ' + REPLACE(ERROR_MESSAGE(),'"','''') + '"}'
					END CATCH
				END ELSE BEGIN
					-- Rowcount <> 0, so do an update, assuming the beginning and ending values are different
					-- if they are not different, skip and proceed to the next attribute
					IF @AttributeValue <> @OldAttributeValue BEGIN
						BEGIN TRY
							IF @DataType = 'String'
								UPDATE Attributes SET AttributeValueString = @AttributeValue, Approved = @ApprovalValue 
								WHERE EntityID = @DataID AND AttributeID = @AttributeID
							ELSE IF @DataType = 'Boolean'
								UPDATE Attributes SET AttributeValueBit = IIF(@AttributeValue = 'False',0,1), Approved = @ApprovalValue 
								WHERE EntityID = @DataID AND AttributeID = @AttributeID
							ELSE IF @DataType = 'Date' BEGIN
								-- Make sure the date does not have a .00 at the end, because that will break
								SET @AttributeValue = CASE WHEN CHARINDEX('.00',@AttributeValue) <> 0 
									THEN SUBSTRING(@AttributeValue,1,CHARINDEX('.00',@AttributeValue)-1) 
									ELSE @AttributeValue END
								UPDATE Attributes SET AttributeValueDateTime = CAST(@AttributeValue AS DateTime), Approved = @ApprovalValue 
								WHERE EntityID = @DataID AND AttributeID = @AttributeID
							END ELSE IF @DataType = 'Number'
								UPDATE Attributes SET AttributeValueNumber = CAST(@AttributeValue AS numeric(18, 0)), Approved = @ApprovalValue 
								WHERE EntityID = @DataID AND AttributeID = @AttributeID
							ELSE IF @DataType = 'TimeDiv'
								UPDATE Attributes SET AttributeValueString = @AttributeValue, Approved = @ApprovalValue 
								WHERE EntityID = @DataID AND AttributeID = @AttributeID

							INSERT INTO UndoAttributes(EntityID,AttributeID,OldData,NewData,
							UndoAction,ChangedBy,WhenChanged)
							VALUES(@DataID,@AttributeID,@OldAttributeValue,@AttributeValue,
							'Update',@RequestorEntityID,GETDATE())
						END TRY
						BEGIN CATCH
							SET @ErrorString = '{"DataError":"From EntityAttributes ' + REPLACE(ERROR_MESSAGE(),'"','''') + '"}'
						END CATCH
					END
				END
			END ELSE IF NOT @OldAttributeValue IS NULL AND ((@EntitySecurity > 4 AND @RequestedAction = 'DeleteEntity' AND (NOT @DeleteOverride IS NULL AND @DeleteOverride = 'True'))
					OR (@EntitySecurity > 3 AND @RequestedAction = 'DeleteAttribute' AND NOT @DeleteAttribute IS NULL 
						AND @DeleteAttribute = @AttributeName)) BEGIN
				-- The attribute can be deleted if it exists and either 
				-- the entity is being deleted and there is a delete override and the user has rights to delete entities
				-- or the user has delete rights to the attribute
				BEGIN TRY
					DELETE FROM Attributes 
					WHERE EntityID = @DataID AND AttributeID = @AttributeID

					INSERT INTO UndoAttributes(EntityID,AttributeID,OldData,NewData,
											   UndoAction,ChangedBy,WhenChanged)
					VALUES(@DataID,@AttributeID,@OldAttributeValue,null,
						  'Delete',@RequestorEntityID,GETDATE())
				END TRY
				BEGIN CATCH
					SET @ErrorString = '{"DataError":"From EntityAttributes ' + REPLACE(ERROR_MESSAGE(),'"','''') + '"}'
				END CATCH
			END
			FETCH NEXT FROM @AttributesLoop
			INTO @AttributeID,@AttributeName,@DataType
		END
		CLOSE @AttributesLoop
		DEALLOCATE @AttributesLoop
	END

	IF @RequestedAction = 'DeleteEntity' AND (NOT @DeleteOverride IS NULL AND @DeleteOverride = 'True') 
		AND @EntitySecurity > 4 AND @ErrorString IS NULL BEGIN
		BEGIN TRY
			-- Override has been specified, so delete all relationships and then the entity.
			DECLARE @RelationshipsLoop CURSOR
			SET @RelationshipsLoop = CURSOR FAST_FORWARD
			FOR 
				SELECT Entity1ID,Entity2ID,ShortRelationshipName,StartDate,EndDate
				FROM EntityRelationships
				WHERE Entity1ID = @DataID OR Entity2ID = @DataID
		
			OPEN @RelationshipsLoop
			FETCH NEXT FROM @RelationshipsLoop
			INTO @Entity1ID,@Entity2ID,@ShortRelationshipName,@StartDate,@EndDate
			WHILE @@FETCH_STATUS = 0 AND @ErrorString IS NULL
			BEGIN
				DELETE FROM #JSONTable
				INSERT INTO #JSONTable
				SELECT * FROM parseJSON('{"Entity1ID":"' + cast(@Entity1ID as nvarchar(10)) + 
					'","Entity2ID":"' + cast(@Entity2ID as nvarchar(10)) + 
					'","ShortRelationshipName":"' + @ShortRelationshipName + '"' +
					',"DeleteOverride":"True"' 
					+ COALESCE(',"StartDate":"' + cast(@StartDate as nvarchar(50)) + '"','') +
					+ COALESCE(',"EndDate":"' + cast(@EndDate as nvarchar(50)) + '"','') + '}')
				EXECUTE WriteDataToDB_EntityRelationships @RequestorEntityID,'Delete'
				SELECT @ErrorString = StringValue FROM #JSONTable WHERE [NAME] = 'DataError'
				IF NOT @ErrorString IS NULL
					SET @ErrorString = '{"DataError":"' + @ErrorString + '"}'

				FETCH NEXT FROM @RelationshipsLoop
				INTO @AttributeID,@AttributeName,@DataType,@StartDate,@EndDate
			END
			CLOSE @RelationshipsLoop
			DEALLOCATE @RelationshipsLoop
			DELETE FROM #JSONTable
			INSERT INTO #JSONTable
			SELECT * FROM parseJSON(@JSONData)


			DELETE FROM Entities
			WHERE EntityID = @DataID

			INSERT INTO UndoOtherChanges(UndoAction,ChangedBy,WhenChanged,ChangeLog)
			VALUES('Delete',@RequestorEntityID,GETDATE(),
				'Deleted entity with an ID of ' + cast(@DataID AS nvarchar(10)) + ' entity type of ' + 
				@EntityType + ' and a subtype of ' +
				COALESCE(@EntitySubType,'') + ' that contained the following json data ' + 
				@JSONData);
		END TRY
		BEGIN CATCH
			SET @ErrorString = '{"DataError":"From EntityAttributes ' + REPLACE(ERROR_MESSAGE(),'"','''') + '"}'
		END CATCH
	END
	ELSE IF @RequestedAction = 'DeleteEntity' AND @ErrorString IS NULL BEGIN
		SET @ErrorString = '{"DataError":"From EntityAttributes - The request was to delete the entity, but no override was specified."}'
	END ELSE BEGIN
		-- The Request was not to delete the entity, so update the entityname
		DECLARE @NOSQL NVARCHAR(MAX)
		DECLARE @EntityName NVARCHAR(MAX)
		SET @NOSQL = dbo.convertrafikinosql('SELECT @@EntityName = EntityName FROM {' +
						@EntityType + COALESCE('.' + @EntitySubType,'') + '} WHERE EntityID = "' + CAST(@DataID AS NVARCHAR) + '"'
						,default,default,default,default);
		EXECUTE sp_executesql  
			@stmt = @NOSQL,
			@params = N'@EntityName NVARCHAR(MAX) OUTPUT',
			@EntityName = @EntityName OUTPUT
		UPDATE Entities SET EntityName = @EntityName WHERE EntityID = @DataID
	END



	IF @ErrorString IS NULL BEGIN
		COMMIT TRANSACTION WriteEntityData;
		RETURN 0;
	END
	ELSE BEGIN
		BEGIN TRY
			ROLLBACK TRANSACTION WriteEntityData;
		END TRY
		BEGIN CATCH
			-- take the } and the trailing " off the end of the ErrorString
			SET @ErrorString = SUBSTRING(@ErrorString,1,LEN(@ErrorString)-2)
			SET @ErrorString = @ErrorString + ' Additionally, the system errored when attempting to rollback the transaction for entity '+CAST(@DataID AS NVARCHAR)+'."}'
		END CATCH
		DELETE FROM #JSONTable
		INSERT INTO #JSONTable
		SELECT * FROM parseJSON(@ErrorString)
		RETURN 1
	END

END
